package dao;

import bean.Student;
import util.DriverManegerUitl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class StudentDao {
    private Connection conn;
    private PreparedStatement per;
    private ResultSet resultSet;


    /**
     *登录
     * @param student
     * @return 封装好的student对象
     */
    //封装到student对象里一起传过去
    public Student login(Student student){
        conn= DriverManegerUitl.getConnection();
        try{
            per=conn.prepareStatement("select * from student where username=? and password=?");
            //根据用户名和密码查询
            per.setString(1,student.getUsername());
            per.setString(2,student.getPassword());
            resultSet = per.executeQuery();
            while (resultSet.next()){
                student.setStudentid(resultSet.getString("studentid"));//学号
                student.setStudentname(resultSet.getString("studentname"));//姓名
            }



            if (null==student.getStudentid()){
                return null;
            }else {
                return student;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,per,resultSet);
        }
        return null;
    }

    /**
     *根据名字返回学号
     * @param studentname
     * @return 学号
     */
    //根据姓名返回学号

    public String getStudentidByUsername(String studentname){
        conn=DriverManegerUitl.getConnection();
        try {
            per=conn.prepareStatement("select * from student where studentname=?");
            per.setString(1,studentname);
            resultSet=per.executeQuery();
            String studentid=null;
            while (resultSet.next()){
                 studentid = resultSet.getString("studentid");
            }
            if (null==studentid){
                System.out.println("宿舍号null");
                return null;
            }else {
                return studentid;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,per,resultSet);
        }
        return null;
    }

    /**
     * 根据用户名返回宿舍号
     * @param username
     * @return 宿舍号
     */
//    根据用户名返回宿舍号
public String getDomitoryidByUsername(String username){
        conn=DriverManegerUitl.getConnection();
        try {
            per=conn.prepareStatement("select * from student where username=?");
            per.setString(1,username);
            resultSet=per.executeQuery();
            while (resultSet.next()){
                String dormitoryid = resultSet.getString("dormitoryid");
                if (dormitoryid==null){
                    System.out.println("宿舍号为空");
                    return null;
                }else {
                    return dormitoryid;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,per,resultSet);
        }
        return null;

}
    /**
     * 根据宿舍号返回宿舍成员
     * @param dormitoryid
     * @return  列表
     */
    //根据宿舍号返回宿舍成员
    public ArrayList<Student> getStudentsByDormintoryid(String dormitoryid){
        ArrayList<Student> students=new ArrayList<>();
        conn=DriverManegerUitl.getConnection();
        try {
            per=conn.prepareStatement("select * from student where dormitoryid=?");
            per.setString(1,dormitoryid);
            resultSet=per.executeQuery();
            while (resultSet.next()){
                Student stu=new Student();
                stu.setStudentid(resultSet.getString("studentid"));
                stu.setStudentname(resultSet.getString("studentname"));
                stu.setMajor(resultSet.getString("major"));
                stu.setGender(resultSet.getString("gender"));
                stu.setDepartment(resultSet.getString("department"));
                stu.setClasses(resultSet.getString("classes"));
                stu.setDormitoryid(resultSet.getString("dormitoryid"));
                stu.setPhoneid(resultSet.getString("phoneid"));
                stu.setEntrytime(resultSet.getDate("entrytime"));

                students.add(stu);
            }
                if (students.size()==0){
                    System.out.println("列表为null");
                    return null;
                }else {
                    return students;
                }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            DriverManegerUitl.closed(conn,per,resultSet);
        }
        return null;
    }

    /**
     * 用户名返回姓名
     * @param username
     * @return
     */
    //根据用户名返回姓名
    public String getStudentnameByUsername(String username){
        conn=DriverManegerUitl.getConnection();
        try {
            per=conn.prepareStatement("select * from student where username=?");
            per.setString(1,username);
            resultSet= per.executeQuery();
            String studentname=null;
            while (resultSet.next()){
                 studentname = resultSet.getString("studentname");//获取姓名
            }
            if (studentname==null){
                return null;
            }else {
                return studentname;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,per,resultSet);
        }
        return null;
    }


    public void insertSQB(Student student) {
        conn = DriverManegerUitl.getConnection();
        try {
            String sql="insert into student(studentid,studentname,major,gender,department,classes,dormitoryid,phoneid,entrytime,username,password) values(?,?,?,?,?,?,?,?,?,?,?)";
            per=conn.prepareStatement(sql);

            per.setString(1,student.getStudentid());
            per.setString(2,student.getStudentname());
            per.setString(3,student.getMajor());
            per.setString(4,student.getGender());
            per.setString(5,student.getDepartment());
            per.setString(6,student.getClasses());
            per.setString(7,student.getDormitoryid());
            per.setString(8,student.getPhoneid());
            per.setDate(9,new java.sql.Date(student.getEntrytime().getTime()));
            per.setString(10,student.getUsername());
            per.setString(11,student.getPassword());

            per.executeUpdate();


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,per,resultSet);

        }
    }

    public void  updateSQB(Student student){
        conn = DriverManegerUitl.getConnection();
        try {
            String sql = "update student set password=? where studentid=?";
            per=conn.prepareStatement(sql);
            per.setString(1,student.getPassword());
            per.setString(2,student.getStudentid());
            per.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,per,resultSet);

        }

    }
    //找回密码
    public  String returnPassword(String username,String studentname){
        conn = DriverManegerUitl.getConnection();
        try {
            per=conn.prepareStatement("select password from student where username=? and studentname=?");
            per.setString(1,username);
            per.setString(2,studentname);
            resultSet=per.executeQuery();
            String password = null;
            while (resultSet.next()){
                password = resultSet.getString("password");
            }
            return password;

        } catch (SQLException throwables) {
            throwables.printStackTrace();

        } finally {
            DriverManegerUitl.closed(conn,per,resultSet);
        }
        return null;
    }

//    删除学生
    public  void delInfo(String studentid){
        conn = DriverManegerUitl.getConnection();
        try {
            per=conn.prepareStatement("delete from student where studentid=?");
            per.setString(1,studentid);
            per.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,per,resultSet);
        }
    }
}
